IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdDetail_Update'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdDetail_Update
GO

CREATE PROC dbo.pInProdDetail_Update
	@productId INT,
	@amount INT,
	@inputPrice INT,
	@inPaperDetailId INT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo        INT,
    @strErrMsg       NVARCHAR(400),
    @intReturn       INT,
    @oldAmount		 INT,
	@intBillDetailId	INT,
	@intOldInputPrice	INT
	

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/

	BEGIN TRANSACTION
	BEGIN TRY		
		SELECT @oldAmount = InOutProdDetail.Amount,
			@intOldInputPrice = InOutProdDetail.InputPrice
		FROM InOutProdDetail
		WHERE Id = @inPaperDetailId
	
		-- Update InProdDetail
		UPDATE InOutProdDetail
		SET Amount = @amount , 
			InputPrice = @inputPrice
		WHERE Id = @inPaperDetailId
		
		IF @oldAmount <> @amount BEGIN
			UPDATE Product
			SET Amount = Amount - @oldAmount + @amount
			WHERE Id = @productId			
		END -- end compare old amount vs new amount
		
		IF @intOldInputPrice <> @inputPrice BEGIN
			-- Update latest input price of product
			EXEC pUpdateLatestInputPriceOfProduct
				@vintProductId = @productId
		END
		
		SET @intReturn = 1
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
	  ROLLBACK TRANSACTION
	END CATCH
		
	SELECT @intReturn 



